/**
 * 数据库迁移脚本：为 character_resources 表添加钱庄余额字段
 * 执行前请确保已备份数据库
 */

const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库配置（支持环境变量和默认值）
const dbConfig = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
    database: process.env.DB_NAME || 'nextjs',
    timezone: '+08:00',
    charset: 'utf8mb4'
};

// 迁移SQL语句
const migrationSQL = [
    // 1. 添加钱庄余额字段
    `ALTER TABLE \`character_resources\` 
     ADD COLUMN \`balanceGold\` bigint NOT NULL DEFAULT '0' COMMENT '钱庄金币余额' AFTER \`bdSilver\`,
     ADD COLUMN \`balanceMoney\` bigint NOT NULL DEFAULT '0' COMMENT '钱庄银两余额' AFTER \`balanceGold\`;`,

    // 2. 为新字段添加索引
    `ALTER TABLE \`character_resources\` 
     ADD INDEX \`idx_balanceGold\` (\`balanceGold\`),
     ADD INDEX \`idx_balanceMoney\` (\`balanceMoney\`);`,

    // 3. 初始化现有记录的余额字段（将现有金币和银两余额复制到钱庄余额字段）
    `UPDATE \`character_resources\` 
     SET 
       \`balanceGold\` = \`gold\`,
       \`balanceMoney\` = \`silver\`
     WHERE \`balanceGold\` = 0 AND \`balanceMoney\` = 0;`
];

// 验证SQL语句
const validationSQL = [
    // 检查表结构
    `DESCRIBE \`character_resources\`;`,

    // 检查新字段是否存在
    `SELECT 
        TABLE_NAME,
        COLUMN_NAME,
        DATA_TYPE,
        COLUMN_DEFAULT,
        IS_NULLABLE,
        COLUMN_COMMENT
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
         AND TABLE_NAME = 'character_resources'
         AND COLUMN_NAME IN ('balanceGold', 'balanceMoney')
     ORDER BY COLUMN_NAME;`,

    // 检查索引
    `SHOW INDEX FROM \`character_resources\` WHERE Key_name IN ('idx_balanceGold', 'idx_balanceMoney');`,

    // 检查数据初始化结果
    `SELECT 
        COUNT(*) as total_records,
        SUM(CASE WHEN balanceGold = gold THEN 1 ELSE 0 END) as gold_initialized,
        SUM(CASE WHEN balanceMoney = silver THEN 1 ELSE 0 END) as money_initialized
     FROM \`character_resources\`;`
];

class DatabaseMigration {
    constructor() {
        this.connection = null;
        this.migrationResults = [];
    }

    // 显示数据库配置信息
    showDatabaseConfig() {
        console.log('🔧 数据库配置信息:');
        console.log('='.repeat(50));
        console.log(`主机: ${dbConfig.host}`);
        console.log(`端口: ${dbConfig.port}`);
        console.log(`用户: ${dbConfig.user}`);
        console.log(`数据库: ${dbConfig.database}`);
        console.log(`时区: ${dbConfig.timezone}`);
        console.log('='.repeat(50));
        console.log('');
    }

    // 连接数据库
    async connect() {
        try {
            console.log('🔌 正在连接数据库...');
            this.connection = await mysql.createConnection(dbConfig);
            console.log('✅ 数据库连接成功');

            // 设置时区
            await this.connection.execute("SET time_zone = '+08:00'");
            console.log('✅ 时区设置完成');

        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    // 检查表是否存在
    async checkTableExists() {
        try {
            console.log('📋 检查 character_resources 表是否存在...');
            const [rows] = await this.connection.execute(
                "SHOW TABLES LIKE 'character_resources'"
            );

            if (rows.length === 0) {
                throw new Error('character_resources 表不存在，请先创建该表');
            }

            console.log('✅ character_resources 表存在');

            // 检查表结构
            const [describeRows] = await this.connection.execute(
                "DESCRIBE `character_resources`"
            );
            console.log(`📊 当前表结构: ${describeRows.length} 个字段`);

            return true;
        } catch (error) {
            console.error('❌ 表检查失败:', error.message);
            throw error;
        }
    }

    // 检查字段是否已存在
    async checkFieldsExist() {
        try {
            console.log('🔍 检查新字段是否已存在...');
            const [rows] = await this.connection.execute(
                `SELECT COLUMN_NAME 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_SCHEMA = DATABASE() 
                     AND TABLE_NAME = 'character_resources'
                     AND COLUMN_NAME IN ('balanceGold', 'balanceMoney')`
            );

            if (rows.length > 0) {
                const existingFields = rows.map(row => row.COLUMN_NAME);
                console.log(`⚠️  以下字段已存在: ${existingFields.join(', ')}`);
                return existingFields;
            }

            console.log('✅ 新字段不存在，可以安全添加');
            return [];
        } catch (error) {
            console.error('❌ 字段检查失败:', error.message);
            throw error;
        }
    }

    // 执行迁移SQL
    async executeMigration() {
        try {
            console.log('🚀 开始执行数据库迁移...');
            console.log('');

            for (let i = 0; i < migrationSQL.length; i++) {
                const sql = migrationSQL[i];
                const stepName = [
                    '添加钱庄余额字段',
                    '添加索引',
                    '初始化数据'
                ][i];

                console.log(`📝 步骤 ${i + 1}: ${stepName}`);
                console.log(`SQL: ${sql.substring(0, 100)}...`);

                try {
                    const [result] = await this.connection.execute(sql);
                    console.log(`✅ 步骤 ${i + 1} 执行成功`);

                    this.migrationResults.push({
                        step: i + 1,
                        name: stepName,
                        success: true,
                        result: result
                    });

                } catch (error) {
                    console.error(`❌ 步骤 ${i + 1} 执行失败:`, error.message);

                    this.migrationResults.push({
                        step: i + 1,
                        name: stepName,
                        success: false,
                        error: error.message
                    });

                    // 如果是字段已存在的错误，可以忽略
                    if (error.message.includes('Duplicate column name')) {
                        console.log('⚠️  字段已存在，跳过此步骤');
                        continue;
                    }

                    throw error;
                }

                console.log('');
            }

            console.log('🎉 数据库迁移执行完成');

        } catch (error) {
            console.error('❌ 迁移执行失败:', error.message);
            throw error;
        }
    }

    // 验证迁移结果
    async validateMigration() {
        try {
            console.log('🔍 验证迁移结果...');
            console.log('');

            for (let i = 0; i < validationSQL.length; i++) {
                const sql = validationSQL[i];
                const validationName = [
                    '表结构检查',
                    '新字段检查',
                    '索引检查',
                    '数据初始化检查'
                ][i];

                console.log(`🔍 ${validationName}:`);
                try {
                    const [rows] = await this.connection.execute(sql);

                    if (i === 0) {
                        // 表结构
                        console.log(`   字段数量: ${rows.length}`);
                        const newFields = rows.filter(row =>
                            row.Field === 'balanceGold' || row.Field === 'balanceMoney'
                        );
                        if (newFields.length > 0) {
                            newFields.forEach(field => {
                                console.log(`   ✅ ${field.Field}: ${field.Type} ${field.Null === 'NO' ? 'NOT NULL' : 'NULL'} DEFAULT ${field.Default}`);
                            });
                        }
                    } else if (i === 1) {
                        // 新字段检查
                        if (rows.length === 2) {
                            console.log('   ✅ balanceGold 和 balanceMoney 字段已成功添加');
                        } else {
                            console.log(`   ⚠️  只找到 ${rows.length} 个新字段`);
                        }
                    } else if (i === 2) {
                        // 索引检查
                        if (rows.length === 2) {
                            console.log('   ✅ 新字段索引已成功创建');
                        } else {
                            console.log(`   ⚠️  只找到 ${rows.length} 个新索引`);
                        }
                    } else if (i === 3) {
                        // 数据初始化检查
                        if (rows.length > 0) {
                            const data = rows[0];
                            console.log(`   📊 总记录数: ${data.total_records}`);
                            console.log(`   📊 金币初始化: ${data.gold_initialized}/${data.total_records}`);
                            console.log(`   📊 银两初始化: ${data.money_initialized}/${data.total_records}`);
                        }
                    }

                } catch (error) {
                    console.log(`   ❌ 验证失败: ${error.message}`);
                }

                console.log('');
            }

        } catch (error) {
            console.error('❌ 验证失败:', error.message);
        }
    }

    // 显示迁移结果摘要
    showMigrationSummary() {
        console.log('📊 迁移结果摘要:');
        console.log('='.repeat(50));

        const totalSteps = this.migrationResults.length;
        const successfulSteps = this.migrationResults.filter(r => r.success).length;
        const failedSteps = totalSteps - successfulSteps;

        this.migrationResults.forEach(result => {
            const status = result.success ? '✅' : '❌';
            console.log(`${status} 步骤 ${result.step}: ${result.name}`);
            if (!result.success) {
                console.log(`   错误: ${result.error}`);
            }
        });

        console.log('='.repeat(50));
        console.log(`总计: ${successfulSteps}/${totalSteps} 个步骤成功`);

        if (failedSteps === 0) {
            console.log('🎉 所有迁移步骤执行成功！');
        } else {
            console.log(`⚠️  ${failedSteps} 个步骤失败，请检查日志`);
        }
    }

    // 关闭数据库连接
    async close() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    // 运行完整迁移流程
    async run() {
        try {
            console.log('🚀 开始执行数据库迁移脚本');
            console.log('📅 执行时间:', new Date().toLocaleString('zh-CN', { timeZone: 'Asia/Shanghai' }));
            console.log('');

            // 显示配置信息
            this.showDatabaseConfig();

            // 连接数据库
            await this.connect();

            // 检查表是否存在
            await this.checkTableExists();

            // 检查字段是否已存在
            const existingFields = await this.checkFieldsExist();

            if (existingFields.length === 2) {
                console.log('ℹ️  所有字段已存在，跳过迁移');
                console.log('');
            } else {
                // 执行迁移
                await this.executeMigration();
                console.log('');
            }

            // 验证迁移结果
            await this.validateMigration();

            // 显示结果摘要
            this.showMigrationSummary();

        } catch (error) {
            console.error('💥 迁移过程中发生错误:', error.message);
            console.error('请检查数据库连接和权限设置');
        } finally {
            // 关闭连接
            await this.close();
        }
    }
}

// 主函数
async function main() {
    const migration = new DatabaseMigration();
    await migration.run();
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = DatabaseMigration;
